﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonModule;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using ePlanet.ArchiveMange.Models;
using System.Data;
using System.Data.Common;

namespace ePlanet.ArchiveMange.DataAccess
{
    public class PageFileDAL : DataHelper
    {
        public PageFileDAL()
        {
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public ArchiveMangeModal.PAGEFILEDataTable GetAllDocumenArchivetData()
        {
            string sql = "select * from PAGEFILE";
            ArchiveMangeModal.PAGEFILEDataTable data = new ArchiveMangeModal.PAGEFILEDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    ds.Tables[0].TableName = data.TableName;
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 根据卷ID获取卷内件
        /// </summary>
        /// <param name="archiveId">卷ID</param>
        /// <returns></returns>
        public ArchiveMangeModal.PAGEFILEDataTable GetPgeFileByArchiveID(string archiveId)
        {
            string sql = "select * from PAGEFILE where F_ARCHIVEID='"+archiveId+"'";
            ArchiveMangeModal.PAGEFILEDataTable data = new ArchiveMangeModal.PAGEFILEDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    ds.Tables[0].TableName = data.TableName;
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 根据主键查询数据
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public ArchiveMangeModal.PAGEFILEDataTable GetAllDocumenArchivetDataById(string DATAID)
        {
            string sql = "select * from PageFile where DATAID='" + DATAID + "'";
            ArchiveMangeModal.PAGEFILEDataTable data = new ArchiveMangeModal.PAGEFILEDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }


        public DataTable GetPageFileByFileIdList(IList<string> fileIdList)
        {
            try
            {
                StringBuilder keys = new StringBuilder("");
                foreach (string s in fileIdList)
                {
                    keys.Append("'" + s + "',");
                }
                if (keys.Length > 0)
                {
                    keys = keys.Remove(keys.Length - 1, 1);
                }

                string sql = "select * from pagefile where DATAID in (" + keys + ")";
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                return ds.Tables[0];
            }
            catch
            {
                throw;
            }
        }

        public DataTable GetPageFileCarrierTypeByFileIdList(IList<string> fileIdList)
        {
            if (fileIdList == null || fileIdList.Count() == 0)
            {
                return null;
            }
            var ids = string.Join(",", (from id in fileIdList select string.Format("'{0}'", id)).ToArray());

            string sql = @"SELECT A.DATAID, B.Code_Desc AS F_CARRIERTYPE
  FROM PAGEFILE A
  LEFT JOIN P_BT_CODETABLE B
    ON A.F_CARRIERTYPE = B.CODETABLEID
 where A.DATAID in (" + ids + ")";
            DataSet ds = this.DBHelper.ExecuteDataSet(sql);
            return ds.Tables[0];
        }

        public DataTable GetPageFileByArchiveIdListAndType(IList<string> fileIdList,int archiveTypeId)
        {
            try
            {
                StringBuilder keys = new StringBuilder("");
                foreach (string s in fileIdList)
                {
                    keys.Append("'" + s + "',");
                }
                if (keys.Length > 0)
                {
                    keys = keys.Remove(keys.Length - 1, 1);
                }

                string sql = "select * from pagefile where archivetype = "+archiveTypeId+" and F_archiveid in (" + keys + ")";
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                return ds.Tables[0];
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 改变卷内件的序号
        /// </summary>
        /// <param name="DATAID"></param>
        /// <param name="order"></param>
        public void ChangePageFileOrder(string DATAID, int order)
        {
            string sql = "update pagefile set ordernum=:ordernum where DATAID=:DATAID";
            using (DbCommand cmd = this.DBHelper.database.GetSqlStringCommand(sql))
            {
                this.DBHelper.database.AddParameter(cmd, "ordernum", DbType.String, ParameterDirection.Input, "ordernum", DataRowVersion.Current, order);
                this.DBHelper.database.AddParameter(cmd, "DATAID", DbType.String, ParameterDirection.Input, "DATAID", DataRowVersion.Current, DATAID);               
                this.DBHelper.database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 互换2个卷内件的序号
        /// </summary>
        /// <param name="DATAID"></param>
        /// <param name="DataID2"></param>
        public void MovePageFileOrder(string DATAID, string DataID2)
        {
            string sql1 = string.Empty;
            sql1 = sql1 + " Declare curOrdNo int;";
            sql1 = sql1 + " begin";
            sql1 = sql1 + " Select ordernum INTO curOrdNo from pagefile where DATAID='" + DataID2 + "' ;";
            sql1 = sql1 + " Update pagefile Set ordernum=(Select ordernum From pagefile Where DATAID='" + DATAID + "') Where DATAID='" + DataID2 + "'; ";
            sql1 = sql1 + " Update pagefile Set ordernum=curOrdNo Where DATAID='" + DATAID + "'; ";
            sql1 = sql1 + " end;";
            this.DBHelper.ExecuteSql(sql1);
        }


        /// <summary>
        /// 获取卷内件中最大的序号
        /// </summary>
        /// <param name="ArchiveId">卷ID</param>
        public int GetPageFileMaxOrder(string ArchiveId)
        {
            string sql = "select max(orderNum) as maxOrderNum from pagefile where f_archiveid=:f_archiveid";
            using (DbCommand cmd = this.DBHelper.database.GetSqlStringCommand(sql))
            {
                this.DBHelper.database.AddParameter(cmd, "f_archiveid", DbType.String, ParameterDirection.Input, "f_archiveid", DataRowVersion.Current, ArchiveId);
                object _obj = this.DBHelper.database.ExecuteScalar(cmd);
                int _order = 0;
                int.TryParse(_obj.ToString(), out _order);
                return _order;
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool DeleteData(string key)
        {
            string sql = "delete from pagefile where DATAID=:DATAID";
            int rowcount = 0;
            using (DbCommand cmd = this.DBHelper.database.GetSqlStringCommand(sql))
            {
                this.DBHelper.database.AddParameter(cmd, "DATAID", DbType.String, ParameterDirection.Input, "DATAID", DataRowVersion.Current, key);
                rowcount = this.DBHelper.database.ExecuteNonQuery(cmd);
            }
            return rowcount > 0 ? true : false;
        }

        /// <summary>
        /// 将卷内件从卷中移除
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool RemoveData(string key)
        {
            string sql = "update  pagefile set f_archiveid=null where DATAID=:DATAID";
            int rowcount = 0;
            using (DbCommand cmd = this.DBHelper.database.GetSqlStringCommand(sql))
            {
                this.DBHelper.database.AddParameter(cmd, "DATAID", DbType.String, ParameterDirection.Input, "DATAID", DataRowVersion.Current, key);
                rowcount = this.DBHelper.database.ExecuteNonQuery(cmd);
            }
            return rowcount > 0 ? true : false;
        }

        public void SetPageFileArchiveNum(string archiveId, string archiveNum)
        {
            string sql = "update pagefile set archivenum=:archivenum where f_archiveid=:f_archiveid";
            using (DbCommand cmd = this.DBHelper.database.GetSqlStringCommand(sql))
            {
                this.DBHelper.database.AddParameter(cmd, "archivenum", DbType.String, ParameterDirection.Input, "archivenum", DataRowVersion.Current, archiveNum);
                this.DBHelper.database.AddParameter(cmd, "f_archiveid", DbType.String, ParameterDirection.Input, "f_archiveid", DataRowVersion.Current, archiveId);
                this.DBHelper.database.ExecuteNonQuery(cmd);
            }
        }

        public bool IsSameSecret(List<Guid> dataIDList)
        {
            return true;
        }

        ~PageFileDAL()
        {
            this.DBHelper = null;
        }
    }
}
